// 数据库访问抽象层 - 支持离线和在线模式切换
// import { api } from '@/api' // 在线模式时需要取消注释

// 配置：是否使用在线模式（将来可通过设置切换）
const USE_ONLINE_MODE = false // 暂时使用离线模式

// SQLite数据库实例
let db: any = null

// 初始化数据库
export const initDatabase = (): Promise<void> => {
  return new Promise((resolve, reject) => {
    if (USE_ONLINE_MODE) {
      // 在线模式：不需要本地数据库
      console.log('📡 使用在线模式')
      resolve()
      return
    }

    // 离线模式：初始化本地SQLite数据库
    if (!(window as any).sqlitePlugin) {
      const error = '❌ SQLite插件未加载！请确保：1) 在Cordova环境中运行 2) 已等待deviceready事件'
      console.error(error)
      alert(error) // 显示错误给用户
      reject(new Error('SQLite plugin not available'))
      return
    }

    try {
      db = (window as any).sqlitePlugin.openDatabase({
        name: 'accounting.db',
        location: 'default'
      })

      console.log('💾 本地数据库已打开')
    } catch (error) {
      console.error('❌ 打开数据库失败:', error)
      alert('数据库打开失败: ' + error)
      reject(error)
      return
    }

    // 创建表结构
    db.transaction((tx: any) => {
      // 分类表
      tx.executeSql(`
        CREATE TABLE IF NOT EXISTS categories (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          name TEXT NOT NULL,
          type TEXT NOT NULL,
          icon TEXT,
          color TEXT,
          isSystem INTEGER DEFAULT 0,
          sortOrder INTEGER DEFAULT 0,
          isActive INTEGER DEFAULT 1,
          createdAt TEXT DEFAULT CURRENT_TIMESTAMP,
          updatedAt TEXT DEFAULT CURRENT_TIMESTAMP
        )
      `)

      // 交易记录表
      tx.executeSql(`
        CREATE TABLE IF NOT EXISTS transactions (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          type TEXT NOT NULL,
          amount REAL NOT NULL,
          categoryId INTEGER,
          date TEXT NOT NULL,
          description TEXT,
          account TEXT DEFAULT 'cash',
          createdAt TEXT DEFAULT CURRENT_TIMESTAMP,
          updatedAt TEXT DEFAULT CURRENT_TIMESTAMP,
          FOREIGN KEY (categoryId) REFERENCES categories(id)
        )
      `)

      // 数据库迁移：检查并添加缺失的字段
      tx.executeSql('PRAGMA table_info(transactions)', [], (tx: any, results: any) => {
        const columns = []
        for (let i = 0; i < results.rows.length; i++) {
          columns.push(results.rows.item(i).name)
        }
        
        // 如果没有description字段，添加它
        if (!columns.includes('description')) {
          console.log('🔄 迁移数据库：添加description字段')
          tx.executeSql('ALTER TABLE transactions ADD COLUMN description TEXT')
        }
        
        // 如果没有account字段，添加它
        if (!columns.includes('account')) {
          console.log('🔄 迁移数据库：添加account字段')
          tx.executeSql('ALTER TABLE transactions ADD COLUMN account TEXT DEFAULT "cash"')
        }
        
        // 如果有note字段但没有description字段，复制数据
        if (columns.includes('note') && !columns.includes('description')) {
          console.log('🔄 迁移数据库：复制note到description')
          tx.executeSql('UPDATE transactions SET description = note WHERE description IS NULL')
        }
      })

      // 初始化默认分类
      tx.executeSql('SELECT COUNT(*) as count FROM categories', [], (tx: any, results: any) => {
        if (results.rows.item(0).count === 0) {
          console.log('📝 初始化默认分类...')
          const defaultCategories = [
            // 收入分类
            { name: '工资收入', type: 'income', icon: 'salary', color: '#10B981', isSystem: 1, sortOrder: 1 },
            { name: '奖金', type: 'income', icon: 'bonus', color: '#F59E0B', isSystem: 1, sortOrder: 2 },
            { name: '投资收益', type: 'income', icon: 'investment', color: '#3B82F6', isSystem: 1, sortOrder: 3 },
            { name: '兼职收入', type: 'income', icon: 'part-time', color: '#8B5CF6', isSystem: 1, sortOrder: 4 },
            { name: '其他收入', type: 'income', icon: 'other', color: '#6B7280', isSystem: 1, sortOrder: 5 },
            // 支出分类
            { name: '餐饮', type: 'expense', icon: 'food', color: '#EF4444', isSystem: 1, sortOrder: 1 },
            { name: '交通', type: 'expense', icon: 'transport', color: '#F97316', isSystem: 1, sortOrder: 2 },
            { name: '购物', type: 'expense', icon: 'shopping', color: '#EC4899', isSystem: 1, sortOrder: 3 },
            { name: '娱乐', type: 'expense', icon: 'entertainment', color: '#8B5CF6', isSystem: 1, sortOrder: 4 },
            { name: '医疗', type: 'expense', icon: 'medical', color: '#06B6D4', isSystem: 1, sortOrder: 5 },
            { name: '教育', type: 'expense', icon: 'education', color: '#84CC16', isSystem: 1, sortOrder: 6 },
            { name: '住房', type: 'expense', icon: 'housing', color: '#F59E0B', isSystem: 1, sortOrder: 7 },
            { name: '通讯', type: 'expense', icon: 'communication', color: '#3B82F6', isSystem: 1, sortOrder: 8 },
            { name: '其他支出', type: 'expense', icon: 'other', color: '#6B7280', isSystem: 1, sortOrder: 9 }
          ]

          defaultCategories.forEach(cat => {
            tx.executeSql(
              'INSERT INTO categories (name, type, icon, color, isSystem, sortOrder, isActive) VALUES (?, ?, ?, ?, ?, ?, 1)',
              [cat.name, cat.type, cat.icon, cat.color, cat.isSystem, cat.sortOrder]
            )
          })
        }
      })
    }, (error: any) => {
      console.error('❌ 数据库初始化失败:', error)
      reject(error)
    }, () => {
      console.log('✅ 数据库初始化成功')
      resolve()
    })
  })
}

// 分类相关操作
export const categoryDB = {
  // 获取所有分类
  async getAll(params?: { type?: string }): Promise<any> {
    if (USE_ONLINE_MODE) {
      return await api.get('/categories', { params })
    }

    return new Promise((resolve, reject) => {
      let sql = 'SELECT * FROM categories WHERE isActive = 1'
      const sqlParams: any[] = []

      if (params?.type) {
        sql += ' AND type = ?'
        sqlParams.push(params.type)
      }

      sql += ' ORDER BY sortOrder ASC'

      db.transaction((tx: any) => {
        tx.executeSql(sql, sqlParams, (tx: any, results: any) => {
          const categories = []
          for (let i = 0; i < results.rows.length; i++) {
            const row = results.rows.item(i)
            categories.push({
              ...row,
              isSystem: Boolean(row.isSystem),
              isActive: Boolean(row.isActive),
              createdAt: row.createdAt,
              updatedAt: row.updatedAt
            })
          }
          resolve({ status: 'success', data: { categories } })
        }, (error: any) => {
          reject(error)
        })
      })
    })
  },

  // 创建分类
  async create(data: any): Promise<any> {
    if (USE_ONLINE_MODE) {
      return await api.post('/categories', data)
    }

    return new Promise((resolve, reject) => {
      const now = new Date().toISOString()
      db.transaction((tx: any) => {
        tx.executeSql(
          'INSERT INTO categories (name, type, icon, color, isSystem, sortOrder, isActive, createdAt, updatedAt) VALUES (?, ?, ?, ?, 0, ?, 1, ?, ?)',
          [data.name, data.type, data.icon || '', data.color || '#6B7280', data.sortOrder || 0, now, now],
          (tx: any, results: any) => {
            resolve({
              status: 'success',
              data: { id: results.insertId, ...data, createdAt: now, updatedAt: now }
            })
          },
          (error: any) => reject(error)
        )
      })
    })
  },

  // 更新分类
  async update(id: number, data: any): Promise<any> {
    if (USE_ONLINE_MODE) {
      return await api.put(`/categories/${id}`, data)
    }

    return new Promise((resolve, reject) => {
      const now = new Date().toISOString()
      db.transaction((tx: any) => {
        tx.executeSql(
          'UPDATE categories SET name = ?, icon = ?, color = ?, updatedAt = ? WHERE id = ?',
          [data.name, data.icon, data.color, now, id],
          () => resolve({ status: 'success', data: { id, ...data, updatedAt: now } }),
          (error: any) => reject(error)
        )
      })
    })
  },

  // 删除分类（软删除）
  async delete(id: number): Promise<any> {
    if (USE_ONLINE_MODE) {
      return await api.delete(`/categories/${id}`)
    }

    return new Promise((resolve, reject) => {
      const now = new Date().toISOString()
      db.transaction((tx: any) => {
        tx.executeSql(
          'UPDATE categories SET isActive = 0, updatedAt = ? WHERE id = ? AND isSystem = 0',
          [now, id],
          () => resolve({ status: 'success', message: '删除成功' }),
          (error: any) => reject(error)
        )
      })
    })
  }
}

// 交易记录相关操作
export const transactionDB = {
  // 获取交易记录列表
  async getAll(params?: any): Promise<any> {
    if (USE_ONLINE_MODE) {
      return await api.get('/transactions', { params })
    }

    return new Promise((resolve, reject) => {
      let sql = `
        SELECT 
          t.*,
          c.name as categoryName,
          c.icon as categoryIcon,
          c.color as categoryColor
        FROM transactions t
        LEFT JOIN categories c ON t.categoryId = c.id
        WHERE 1=1
      `
      const sqlParams: any[] = []

      if (params?.type) {
        sql += ' AND t.type = ?'
        sqlParams.push(params.type)
      }
      if (params?.categoryId) {
        sql += ' AND t.categoryId = ?'
        sqlParams.push(params.categoryId)
      }
      if (params?.startDate) {
        sql += ' AND t.date >= ?'
        sqlParams.push(params.startDate)
      }
      if (params?.endDate) {
        sql += ' AND t.date <= ?'
        sqlParams.push(params.endDate)
      }

      sql += ' ORDER BY t.date DESC, t.createdAt DESC'

      if (params?.limit) {
        sql += ' LIMIT ?'
        sqlParams.push(params.limit)
      }

      db.transaction((tx: any) => {
        tx.executeSql(sql, sqlParams, (tx: any, results: any) => {
          const transactions = []
          for (let i = 0; i < results.rows.length; i++) {
            transactions.push(results.rows.item(i))
          }
          resolve({ status: 'success', data: { transactions, total: results.rows.length } })
        }, (error: any) => reject(error))
      })
    })
  },

  // 创建交易记录
  async create(data: any): Promise<any> {
    if (USE_ONLINE_MODE) {
      return await api.post('/transactions', data)
    }

    return new Promise((resolve, reject) => {
      const now = new Date().toISOString()
      // 将Date对象转换为ISO字符串
      const dateStr = data.date instanceof Date ? data.date.toISOString() : data.date
      
      db.transaction((tx: any) => {
        tx.executeSql(
          'INSERT INTO transactions (type, amount, categoryId, date, description, account, createdAt, updatedAt) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
          [data.type, data.amount, data.categoryId, dateStr, data.description || '', data.account || 'cash', now, now],
          (tx: any, results: any) => {
            console.log('✅ 交易记录已保存，ID:', results.insertId)
            resolve({
              status: 'success',
              data: { id: results.insertId, ...data, createdAt: now, updatedAt: now }
            })
          },
          (error: any) => {
            console.error('❌ 保存交易记录失败:', error)
            reject(error)
          }
        )
      })
    })
  },

  // 更新交易记录
  async update(id: number, data: any): Promise<any> {
    if (USE_ONLINE_MODE) {
      return await api.put(`/transactions/${id}`, data)
    }

    return new Promise((resolve, reject) => {
      const now = new Date().toISOString()
      const dateStr = data.date instanceof Date ? data.date.toISOString() : data.date
      
      db.transaction((tx: any) => {
        tx.executeSql(
          'UPDATE transactions SET type = ?, amount = ?, categoryId = ?, date = ?, description = ?, account = ?, updatedAt = ? WHERE id = ?',
          [data.type, data.amount, data.categoryId, dateStr, data.description || '', data.account || 'cash', now, id],
          () => resolve({ status: 'success', data: { id, ...data, updatedAt: now } }),
          (error: any) => reject(error)
        )
      })
    })
  },

  // 删除交易记录
  async delete(id: number): Promise<any> {
    if (USE_ONLINE_MODE) {
      return await api.delete(`/transactions/${id}`)
    }

    return new Promise((resolve, reject) => {
      db.transaction((tx: any) => {
        tx.executeSql(
          'DELETE FROM transactions WHERE id = ?',
          [id],
          () => resolve({ status: 'success', message: '删除成功' }),
          (error: any) => reject(error)
        )
      })
    })
  },

  // 清除所有交易记录
  async deleteAll(): Promise<any> {
    if (USE_ONLINE_MODE) {
      return await api.delete('/transactions/all')
    }

    return new Promise((resolve, reject) => {
      db.transaction((tx: any) => {
        tx.executeSql(
          'DELETE FROM transactions',
          [],
          (tx: any, results: any) => {
            console.log('✅ 已清除所有交易记录')
            resolve({ status: 'success', message: '清除成功' })
          },
          (error: any) => {
            console.error('❌ 清除交易记录失败:', error)
            reject(error)
          }
        )
      })
    })
  }
}

// 统计相关操作
export const statisticsDB = {
  // 获取概览统计
  async getOverview(params: { startDate: string; endDate: string }): Promise<any> {
    if (USE_ONLINE_MODE) {
      return await api.get('/statistics/overview', { params })
    }

    return new Promise((resolve, reject) => {
      db.transaction((tx: any) => {
        // 获取收入统计
        tx.executeSql(
          'SELECT COALESCE(SUM(amount), 0) as totalIncome, COUNT(*) as incomeCount FROM transactions WHERE type = ? AND date >= ? AND date <= ?',
          ['income', params.startDate, params.endDate],
          (tx: any, incomeResults: any) => {
            const totalIncome = incomeResults.rows.item(0).totalIncome
            const incomeCount = incomeResults.rows.item(0).incomeCount

            // 获取支出统计
            tx.executeSql(
              'SELECT COALESCE(SUM(amount), 0) as totalExpense, COUNT(*) as expenseCount FROM transactions WHERE type = ? AND date >= ? AND date <= ?',
              ['expense', params.startDate, params.endDate],
              (tx: any, expenseResults: any) => {
                const totalExpense = expenseResults.rows.item(0).totalExpense
                const expenseCount = expenseResults.rows.item(0).expenseCount

                // 获取交易总数量
                tx.executeSql(
                  'SELECT COUNT(*) as count FROM transactions WHERE date >= ? AND date <= ?',
                  [params.startDate, params.endDate],
                  (tx: any, countResults: any) => {
                    resolve({
                      status: 'success',
                      data: {
                        totalIncome,
                        totalExpense,
                        balance: totalIncome - totalExpense,
                        transactionCount: countResults.rows.item(0).count,
                        incomeCount,
                        expenseCount
                      }
                    })
                  },
                  (error: any) => reject(error)
                )
              },
              (error: any) => reject(error)
            )
          },
          (error: any) => reject(error)
        )
      })
    })
  },

  // 获取分类统计
  async getCategories(params: { startDate: string; endDate: string }): Promise<any> {
    if (USE_ONLINE_MODE) {
      return await api.get('/statistics/categories', { params })
    }

    return new Promise((resolve, reject) => {
      const sql = `
        SELECT 
          c.id as categoryId,
          c.name as categoryName,
          c.type,
          c.icon as categoryIcon,
          c.color as categoryColor,
          COALESCE(SUM(t.amount), 0) as totalAmount,
          COUNT(t.id) as count
        FROM categories c
        LEFT JOIN transactions t ON c.id = t.categoryId 
          AND t.date >= ? AND t.date <= ?
        WHERE c.isActive = 1
        GROUP BY c.id
        HAVING totalAmount > 0
        ORDER BY totalAmount DESC
      `

      db.transaction((tx: any) => {
        tx.executeSql(sql, [params.startDate, params.endDate], (tx: any, results: any) => {
          const categoryStats = []
          for (let i = 0; i < results.rows.length; i++) {
            categoryStats.push(results.rows.item(i))
          }
          resolve({ status: 'success', data: { categoryStats } })
        }, (error: any) => reject(error))
      })
    })
  },

  // 获取月度统计
  async getMonthly(params: { year: number }): Promise<any> {
    if (USE_ONLINE_MODE) {
      return await api.get('/statistics/monthly', { params })
    }

    return new Promise((resolve, reject) => {
      const sql = `
        SELECT 
          strftime('%m', date) as month,
          type,
          SUM(amount) as total
        FROM transactions
        WHERE strftime('%Y', date) = ?
        GROUP BY month, type
        ORDER BY month
      `

      db.transaction((tx: any) => {
        tx.executeSql(sql, [params.year.toString()], (tx: any, results: any) => {
          const monthlyData = []
          for (let i = 0; i < results.rows.length; i++) {
            monthlyData.push(results.rows.item(i))
          }
          resolve({ status: 'success', data: { monthlyData } })
        }, (error: any) => reject(error))
      })
    })
  },

  // 获取每日统计
  async getDaily(params: { startDate: string; endDate: string }): Promise<any> {
    if (USE_ONLINE_MODE) {
      return await api.get('/statistics/daily', { params })
    }

    return new Promise((resolve, reject) => {
      const sql = `
        SELECT 
          date(date) as date,
          SUM(CASE WHEN type = 'income' THEN amount ELSE 0 END) as income,
          SUM(CASE WHEN type = 'expense' THEN amount ELSE 0 END) as expense
        FROM transactions
        WHERE date >= ? AND date <= ?
        GROUP BY date(date)
        ORDER BY date
      `

      db.transaction((tx: any) => {
        tx.executeSql(sql, [params.startDate, params.endDate], (tx: any, results: any) => {
          const dailyStats = []
          for (let i = 0; i < results.rows.length; i++) {
            dailyStats.push(results.rows.item(i))
          }
          resolve({ status: 'success', data: { dailyStats } })
        }, (error: any) => reject(error))
      })
    })
  }
}

